﻿using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Collections.Generic;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Threading;
using MySql.Data.MySqlClient;

namespace excelRead
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@" ______              _ _____                _ ");
            Console.WriteLine(@"|  ____|            | |  __ \              | |");
            Console.WriteLine(@"| |__  __  _____ ___| | |__) |___  __ _  __| |");
            Console.WriteLine(@"|  __| \ \/ / __/ _ \ |  _  // _ \/ _` |/ _` |");
            Console.WriteLine(@"| |____ >  < (_|  __/ | | \ \  __/ (_| | (_| |");
            Console.WriteLine(@"|______/_/\_\___\___|_|_|  \_\___|\__,_|\__,_|");
            Console.Write("\n输入数据库连接地址(localhost):");
            var server = Console.ReadLine();
            Console.Write("\n输入数据库名(Demo):");
            var dbname = Console.ReadLine();
            Console.Write("\n输入数据库登录名(User):");
            var uid = Console.ReadLine();
            Console.Write("\n输入数据库密码(Pwd):");
            var pwd = Console.ReadLine();

            MySqlConnection conn = new MySqlConnection($"server={server};Database={dbname};Uid={uid};Pwd={pwd}");

            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception)
                {
                    
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        Console.WriteLine("\n连接成功！");
                        Console.Write("\n请将Excel文件拖至本窗口:");
                        var filepath = Console.ReadLine();
                        ReadExcel(filepath,conn);
                        Console.ReadLine();
                    }
                    else
                    {
                        Console.WriteLine("\n请检查输入是否有误！");
                        Console.ReadLine();
                    }
                }
            }
        }
        public static void ReadExcel(string filePath, MySqlConnection connection)
        {           
            IWorkbook wk = null;
            string extension = Path.GetExtension(filePath);
            using (FileStream fs = File.OpenRead(filePath))
            {
                if (extension.Equals(".xls"))
                {
                    wk = new HSSFWorkbook(fs);
                    //xls格式
                }
                else
                {
                    wk = new XSSFWorkbook(fs);
                    //xlsx格式
                }

            }
            int num = 0;
            int temp = wk.NumberOfSheets;
            try
            {
                for (int m = 0; m < wk.NumberOfSheets; m++)
                {
                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(m);//工作簿
                    IRow row = sheet.GetRow(0); //读取当前行数据   
                    int lastRowNum = sheet.LastRowNum;//总行数-1
                    //Console.WriteLine(lastRowNum);
                    StringBuilder sb = new StringBuilder();

                    sb.Append("SET NAMES utf8mb4;\n");
                    sb.Append("SET FOREIGN_KEY_CHECKS = 0;\n");
                    //表名
                    string TableName = sheet.GetRow(0).GetCell(0).ToString().Split(" ")[1];
                    //主键
                    string PkName = "";
                    bool pk = false;
                    //表注释
                    string TableComment = sheet.GetRow(0).GetCell(0).ToString().Split(" ")[0];
                    sb.Append(string.Format("DROP TABLE IF EXISTS `{0}`;\n", TableName));
                    sb.Append(string.Format("CREATE TABLE `{0}`  (\n", TableName));

                    for (int i = 2; i <= lastRowNum; i++)
                    {
                        row = sheet.GetRow(i);
                        if (row != null)
                        {
                            int lastCell = row.LastCellNum;
                            sb.Append($"`{row.GetCell(1).ToString()}`");
                            switch (row.GetCell(2).ToString())
                            {
                                case "varchar":
                                    sb.Append($" {row.GetCell(2).ToString()}{"(" + row.GetCell(3).ToString() + ") "}{"CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci "}");
                                    break;
                                case "longtext":
                                    sb.Append($" {row.GetCell(2).ToString()}{"CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci "}");
                                    break;
                                default:
                                    sb.Append($"{row.GetCell(2).ToString()}");
                                    break;
                            }
                            switch (row.GetCell(5).ToString())
                            {
                                case "√":
                                    PkName = row.GetCell(1).ToString();
                                    pk = true;
                                    break;
                                case "✓":
                                    PkName = row.GetCell(1).ToString();
                                    pk = true;
                                    break;
                                default:
                                    break;
                            }
                            switch (row.GetCell(7).ToString())
                            {
                                case "√":
                                    sb.Append($" NULL DEFAULT NULL ");
                                    break;
                                case "":
                                    sb.Append($" NOT NULL ");
                                    break;
                                default:
                                    break;
                            }
                            sb.Append($"COMMENT '{row.GetCell(9).ToString()}', \n");
                        }
                    }
                    sb.Append(string.Format("  PRIMARY KEY (`{0}`) USING BTREE\n", PkName));
                    sb.Append($") ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '{TableComment}' ROW_FORMAT = Dynamic;\n");
                    if (sheet.GetRow(2).GetCell(2).ToString() == "int" && pk)
                    {
                        sb.Append("SET FOREIGN_KEY_CHECKS = 1;\n");
                    }
                    sb.Append("SET FOREIGN_KEY_CHECKS = 1;\n");

                    Console.WriteLine(sb.ToString());

                    var mjk = new DemoDB.MySqlHelper(connection);
                    var mmm = mjk.ExecuteQuery(sb.ToString(), CommandType.Text);
                    Console.WriteLine(sb.ToString());
                    num++;
                    if (num == temp)
                    {
                        Console.WriteLine("\n\n\n\n 执行完成 :)");
                    }
                } 
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}